﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace SP.OpenSource.CDC.Monitor.BLL
{
    class LSNHelper
    {

        DataTable m_dtCDCStateInfo = new DataTable("CDCStateInfo");
        String m_sErrorMessage = String.Empty;
        public bool LoadLSNs(ref DbConnection inDbConnection, ref DbCommand inDbfCommand,
                            ref DbConnection inDbStateConnection, ref DbCommand inDbfCDCStateCommand, ref String sCDCCaptureInstance,
                      ref String sStartLSN, ref String sEndLSN, ref String sMinLSN, ref String sMaxLSN, String sStateTableName,
                      ref bool bStateInfoExists,ref bool bIsStateOutOfRange ,ref String sErrorMessage)
        {
            try
            {

                if (inDbConnection != null)
                {
                    //writetolog("in function LodLSNs");
                    try
                    {
                        String sQuery = String.Empty;
                        String sDBServer = String.Empty;
                        String sDatabase = String.Empty;
                        String sInitialLoadEnd = String.Empty;
                        DbProviderFactory DbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
                        //Parse CDC enabled DB connection string
                        if (false == Utils.ParseConnectionString(inDbConnection.ConnectionString, ref sDBServer,
                                            ref sDatabase, ref sErrorMessage))
                        {
                            sErrorMessage = "Exception raised while fetching change information.";
                            return false;
                        }


                        String sQueryStateInfo = "select * from " + sStateTableName;
                        sQueryStateInfo += " WHERE [CDCDBServer]='" + sDBServer + "' AND ";
                        sQueryStateInfo += " [CDCDatabase]='" + sDatabase + "'";

                        inDbfCDCStateCommand.CommandType = CommandType.Text;
                        inDbfCDCStateCommand.CommandText = sQueryStateInfo;
                        inDbfCDCStateCommand.Connection = inDbStateConnection; 

                        DbDataAdapter adapter = DbFactory.CreateDataAdapter();
                        adapter.SelectCommand = inDbfCDCStateCommand;
                        adapter.Fill(m_dtCDCStateInfo);

                        String sMinLSNQuery = "select upper(sys.fn_varbintohexstr(sys.fn_cdc_get_min_lsn(N'";
                        sMinLSNQuery += sCDCCaptureInstance;
                        sMinLSNQuery += " '))) as MinLSN";

                        //writetolog("    MinMax query: " + sLSNQuery);

                        inDbfCommand.CommandType = CommandType.Text;
                        inDbfCommand.CommandText = sMinLSNQuery;
                        inDbfCommand.Connection = inDbConnection;

                        DbDataReader schemaMinLSNReader = inDbfCommand.ExecuteReader();
                        sMinLSN = String.Empty;
                        sMaxLSN = String.Empty;

                        while (schemaMinLSNReader.Read())
                        {
                            sMinLSN = schemaMinLSNReader["MinLSN"].ToString();
                        }

                        schemaMinLSNReader.Close();

                        sStartLSN = String.Empty;
                        sEndLSN = String.Empty;

                        /*DataRow[] drIniSelect = m_dtCDCStateInfo.Select("Capture='INIT'");
                        if (drIniSelect.Length > 0)
                        {
                            sInitialLoadEnd = drIniSelect[0]["EndState"].ToString();
                        }*/
                        m_sErrorMessage = String.Empty;
                        if (false == CheckStateExists(sCDCCaptureInstance))
                        {
                            bStateInfoExists = false;
                            sErrorMessage = "CDC State cannot be found.";
                            return false;
                        }
                        else
                        {
                            bStateInfoExists = true;
                            DataRow[] drCISelect = m_dtCDCStateInfo.Select("Capture='" + sCDCCaptureInstance + "'");
                            if (drCISelect.Length > 0)
                            {
                                //sStartLSN = drCISelect[0]["StartState"].ToString();
                                sStartLSN = drCISelect[0]["EndState"].ToString();//Previous end is start for this
                            }
                        }


                        //CHECK whether startLSN is in MIN-MAX range.
                        //Check whether EndLSN is > startLSN, if not make endLSN as startLSN
                        //check whether MIN LSN > MAX LSN

                        if (sStartLSN.Length > 0)
                        {
                            //Get next LSN; as EndLSN stored is the last end point
                            sQuery = "select upper(sys.fn_varbintohexstr(sys.fn_cdc_increment_lsn(";
                            sQuery += "sys.fn_cdc_hexstrtobin('";
                            sQuery += sStartLSN;
                            sQuery += "'))))";

                            // writetolog("    next LSN query: " + sQuery);

                            inDbfCommand.CommandType = CommandType.Text;
                            inDbfCommand.CommandText = sQuery;
                            DbDataReader schemaReader = inDbfCommand.ExecuteReader();
                            while (schemaReader.Read())
                            {
                                sStartLSN = schemaReader[0].ToString();
                            }

                            schemaReader.Close();

                        }

                        
                        String sMaxLSNQuery = "select upper(sys.fn_varbintohexstr(sys.fn_cdc_get_max_lsn())) as MaxLSN ";

                        //writetolog("    MinMax query: " + sLSNQuery);
                        inDbfCommand.CommandType = CommandType.Text;
                        inDbfCommand.CommandText = sMaxLSNQuery;


                        DbDataReader schemaLSNReader = inDbfCommand.ExecuteReader();
                        sMinLSN = String.Empty;
                        sMaxLSN = String.Empty;

                        while (schemaLSNReader.Read())
                        {
                            sMaxLSN = schemaLSNReader["MaxLSN"].ToString();
                        }

                        schemaLSNReader.Close();


                        sEndLSN = sMaxLSN;

                        //Check StartLSN & EndLSN are in range.
                        if (false == CheckStateInRange(sStartLSN, sMinLSN, sMaxLSN))
                        {//if not in range make start lsn as current capture min lsn.
                            //This will avoid range out of bound errors
                            //sStartLSN = sMinLSN;
                            bIsStateOutOfRange = true;
                            sStartLSN = sMaxLSN;
                            sEndLSN = sMaxLSN;
                            return true;
                        }

                        bIsStateOutOfRange = false;

                        //Check if start lsn > max lsn
                        String sResult = CompareStates(sStartLSN, sEndLSN);
                        if (sResult == "1>2")
                            sStartLSN = sEndLSN;



                    }
                    catch (System.Data.Common.DbException dbException)
                    {
                        sErrorMessage = dbException.Message;
                        return false;
                    }
                }
                else
                {
                    sErrorMessage = "Exception raised while fetching change information.";
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                sStartLSN = String.Empty;
                sEndLSN = String.Empty;
                sErrorMessage = ex.Message;
                return false;
            }
        }

        String CompareStates(String sState1, String sState2)
        {
            try
            {
                int iResult = String.CompareOrdinal(sState1, sState2);
                String sResult = ((iResult <= 0) ? "1<2" : "1>2");

                return sResult;
            }
            catch
            {
                return String.Empty;
            }
        }
        bool CheckStateInRange(String sStateToCheck, String sMinState, String sMaxState)
        {
            try
            {
                int iResult = String.CompareOrdinal(sMinState, sStateToCheck);
                String sMinInRange = ((iResult <= 0) ? "InMinRange" : "notInMinRange");

                iResult = String.CompareOrdinal(sStateToCheck, sMaxState);
                String sMaxInRange = ((iResult <= 0) ? "InMaxRange" : "notInMaxRange");

                if (sMinInRange == "InMinRange" && sMaxInRange == "InMaxRange")
                    return true;
                return false;
            }
            catch
            {
                return false;
            }
        }
        bool CheckStateExists(String sStateName)
        {
            try
            {
                DataRow[] drSelect = m_dtCDCStateInfo.Select("Capture='" + sStateName + "'");

                if (drSelect.Length > 0)
                    return true;

                m_sErrorMessage = String.Empty;
                return false;
            }
            catch (Exception ex)
            {
                m_sErrorMessage = "CDCStateControl : There was an error while processing CDC state information. ";
                return false;
            }
        }

    }
}
